package com.example.hzly.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.hzly.entity.RouteReview;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

@Mapper
public interface RouteReviewMapper extends BaseMapper<RouteReview> {
    
    @Select("SELECT * FROM route_review WHERE route_id = #{routeId} ORDER BY created_at ASC")
    List<RouteReview> selectByRouteId(@Param("routeId") Long routeId);
    
    @Select("SELECT * FROM route_review WHERE route_id = #{routeId} ORDER BY created_at DESC")
    IPage<RouteReview> selectPageByRouteId(@Param("routeId") Long routeId, Page<RouteReview> page);
    
    @Select("SELECT COALESCE(AVG(rating), 0) FROM route_review WHERE route_id = #{routeId}")
    BigDecimal selectAvgRatingByRouteId(@Param("routeId") Long routeId);
    
    @Select("SELECT COUNT(*) FROM route_review WHERE route_id = #{routeId}")
    Integer selectCountByRouteId(@Param("routeId") Long routeId);
}